const fs = require('fs');
const path = require('path');
const { DataSource } = require('typeorm');

// 数据库配置
const dataSource = new DataSource({
  type: 'mysql',
  host: '43.143.253.188',
  port: 3306,
  username: 'nextjs',
  password: 'yEAiRE2jcxTyRBy5',
  database: 'nextjs',
  entities: [],
  synchronize: false,
  logging: false
});

async function importMapCities() {
  try {
    await dataSource.initialize();
    console.log('✅ 数据库连接成功');

    // 读取地图数据文件
    const mapDataPath = path.join(__dirname, '..', 'output', 'websocket_data_100_sit_v2.json');
    
    if (!fs.existsSync(mapDataPath)) {
      throw new Error(`地图数据文件不存在: ${mapDataPath}`);
    }

    const mapData = JSON.parse(fs.readFileSync(mapDataPath, 'utf8'));
    console.log(`📊 读取到 ${Object.keys(mapData).length} 个地图地点数据`);

    // 检查是否需要创建地图地点表
    let tableExists = false;
    try {
      const result = await dataSource.query(`
        SELECT COUNT(*) as count 
        FROM information_schema.tables 
        WHERE table_schema = 'nextjs' 
        AND table_name = 'map_sites'
      `);
      tableExists = result[0].count > 0;
    } catch (error) {
      console.log('🔍 检查表是否存在时出错，将尝试创建表...');
    }

    if (!tableExists) {
      console.log('🔨 创建 map_sites 表...');
      try {
        await dataSource.query(`
          CREATE TABLE map_sites (
            id INT PRIMARY KEY,
            name VARCHAR(100) NOT NULL COMMENT '地点名称',
            act_id INT NOT NULL COMMENT '活动ID',
            area_id INT NOT NULL COMMENT '区域ID',
            img_type INT NOT NULL COMMENT '图片类型',
            num INT NOT NULL DEFAULT 0 COMMENT '数量',
            ss INT NOT NULL DEFAULT 0 COMMENT '状态值',
            type_id INT NOT NULL COMMENT '类型ID',
            created_at DATETIME NOT NULL COMMENT '创建时间',
            updated_at DATETIME NULL COMMENT '更新时间'
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='地图地点表'
        `);
        console.log('✅ map_sites 表创建成功');
        tableExists = true;
      } catch (error) {
        console.error('❌ 创建表失败:', error.message);
        throw error;
      }
    } else {
      console.log('✅ map_sites 表已存在');
    }

    // 清空现有数据（如果表存在）
    if (tableExists) {
      console.log('🗑️ 清空现有地图地点数据...');
      await dataSource.query('DELETE FROM map_sites');
      console.log('✅ 现有数据已清空');
    }

    // 导入新数据
    console.log('📥 开始导入地图地点数据...');
    let successCount = 0;
    let errorCount = 0;

    for (const [locationName, locationData] of Object.entries(mapData)) {
      try {
        const now = new Date();
        const formattedNow = now.toISOString().slice(0, 19).replace('T', ' ');

        await dataSource.query(`
          INSERT INTO map_sites (
            id, name, act_id, area_id, img_type, num, ss, type_id, created_at, updated_at
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `, [
          locationData.id,
          locationData.name,
          locationData.act_id,
          locationData.area_id,
          locationData.imgType,
          locationData.num,
          locationData.ss,
          locationData.type_id,
          formattedNow,
          formattedNow
        ]);

        successCount++;
        if (successCount % 10 === 0) {
          console.log(`📊 已导入 ${successCount} 个地点...`);
        }
      } catch (error) {
        errorCount++;
        console.error(`❌ 导入地点 "${locationName}" 失败:`, error.message);
      }
    }

    console.log('\n📊 导入完成统计:');
    console.log(`✅ 成功导入: ${successCount} 个地点`);
    console.log(`❌ 导入失败: ${errorCount} 个地点`);

    // 验证导入结果
    console.log('\n🔍 验证导入结果...');
    const totalCount = await dataSource.query('SELECT COUNT(*) as count FROM map_sites');
    console.log(`📊 数据库中总共有 ${totalCount[0].count} 个地图地点`);

    // 显示一些示例数据
    const sampleData = await dataSource.query(`
      SELECT id, name, area_id, type_id, img_type 
      FROM map_sites 
      ORDER BY id 
      LIMIT 10
    `);
    
    console.log('\n📋 示例数据:');
    sampleData.forEach((site, index) => {
      console.log(`${index + 1}. ID: ${site.id}, 名称: ${site.name}, 区域: ${site.area_id}, 类型: ${site.type_id}, 图片类型: ${site.img_type}`);
    });

    // 按区域统计
    console.log('\n📊 按区域统计:');
    const areaStats = await dataSource.query(`
      SELECT area_id, COUNT(*) as count 
      FROM map_sites 
      GROUP BY area_id 
      ORDER BY count DESC 
      LIMIT 10
    `);
    
    areaStats.forEach((stat, index) => {
      console.log(`${index + 1}. 区域 ${stat.area_id}: ${stat.count} 个地点`);
    });

    // 按类型统计
    console.log('\n📊 按类型统计:');
    const typeStats = await dataSource.query(`
      SELECT type_id, COUNT(*) as count 
      FROM map_sites 
      GROUP BY type_id 
      ORDER BY count DESC
    `);
    
    typeStats.forEach((stat, index) => {
      console.log(`${index + 1}. 类型 ${stat.type_id}: ${stat.count} 个地点`);
    });

  } catch (error) {
    console.error('❌ 导入过程中发生错误:', error);
  } finally {
    if (dataSource.isInitialized) {
      await dataSource.destroy();
      console.log('🔌 数据库连接已关闭');
    }
  }
}

// 运行导入
importMapCities().catch(console.error);
